﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using seo.Data;

namespace seo.DAO
{
    class YiLookDao
    {
        private MySqlConnection conn;

        public YiLookDao(MySqlConnection conn)
        {
            this.conn = conn;
        }

        public List<YiLookChapter> GetChaptersByArt(int artid)
        {
            List<YiLookChapter> list = new List<YiLookChapter>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "select artid, chapterid, url, collect_date, upload_flg, upload_date from chapters where artid = '" + artid + "'";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookChapter chapter = new YiLookChapter();
                chapter.artid = reader.GetInt32(0);
                chapter.chapterid = reader.GetInt32(1);
                chapter.url = reader.GetString(2);
                chapter.collect_date = reader.GetDateTime(3);
                chapter.upload_flg = reader.GetString(4);
                chapter.upload_date = reader.GetDateTime(5);
                list.Add(chapter);
            }
            reader.Close();
            return list;
        }

        public List<YiLookChapter> GetUnUploadChaptersByArt(int artid)
        {
            List<YiLookChapter> list = new List<YiLookChapter>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "select artid, chapterid, url, collect_date, upload_flg, upload_date from chapters where upload_flg = '0' and artid = '" + artid + "'";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookChapter chapter = new YiLookChapter();
                chapter.artid = reader.GetInt32(0);
                chapter.chapterid = reader.GetInt32(1);
                chapter.url = reader.GetString(2);
                chapter.collect_date = reader.GetDateTime(3);
                chapter.upload_flg = reader.GetString(4);
                chapter.upload_date = reader.GetDateTime(5);
                list.Add(chapter);
            }
            reader.Close();
            return list;
        }

        public int getMaxId()
        {
            int maxId = 0;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select max(id)+1 as maxId from arts";

            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    maxId = reader.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                maxId = 0;
            }
            reader.Close();
            return maxId;
        }

        public bool IsArtExist(string url)
        {
            bool result = false;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select id from arts where url = '" + url +"'";
            reader = command.ExecuteReader();
            if (reader.Read()) result = true;
            reader.Close();
            return result;
        }

        public bool IsChapterExist(YiLookChapter chapter)
        {
            bool result = false;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select * from chapters where url = '" + chapter.url + "'";
            reader = command.ExecuteReader();
            if (reader.Read()) result = true;

            return result;
        }

        public int InsertArt(string url)
        {
            int result = 0;
            if (IsArtExist(url)) return result;
            string sql = "insert into arts(id, url, collect_date) values(?id, ?url, ?collect_date)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?id", getMaxId());
            cmd.Parameters.AddWithValue("?url", url);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            return result;
        }

        public int InsertChapter(YiLookChapter chapter)
        {
            int result = 0;
            if (IsChapterExist(chapter)) return result;
            string sql = "insert into chapters(artid, chapterid, url, collect_date, upload_flg, upload_date) ";
            sql += "values(?artid, ?chapterid, ?url, ?collect_date, ?upload_flg, ?upload_date)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?artid", chapter.artid);
            cmd.Parameters.AddWithValue("?chapterid", chapter.chapterid);
            cmd.Parameters.AddWithValue("?url", chapter.url);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            cmd.Parameters.AddWithValue("?upload_flg", "0");
            cmd.Parameters.AddWithValue("?upload_date", DateTime.Now);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                
            }
            return result;
        }
    }
}
